/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import POJO.LopPOJO;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author MinhHieu
 */
public class LopDAO {

    public static ArrayList<LopPOJO> selectKhoi_NamHoc(String NamHoc){
        ArrayList<LopPOJO> list = new ArrayList<LopPOJO>();
        try {
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            helper.open();

            String sql = "SELECT DISTINCT Khoi "
                    + "FROM dslop l JOIN dsnamhoc n ON l.MaNamHoc=n.MaNamHoc "
                    + "WHERE NamHoc LIKE '"+NamHoc+"'";
            ResultSet rs = helper.excecuteQuery(sql);
            while (rs.next()) {
                LopPOJO l = new LopPOJO();
                l.setKhoi(rs.getString("Khoi"));
                list.add(l);
            }
            helper.close();

        } catch (Exception ex) {
            System.out.print(ex.getMessage());
        }
        return list;
    }
    public static ArrayList<LopPOJO> selectLop_Khoi(String Khoi){
        ArrayList<LopPOJO> list = new ArrayList<LopPOJO>();
        try {
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            helper.open();

            String sql = "SELECT * "
                    + "FROM dslop "
                    + "WHERE Khoi LIKE '"+Khoi+"'";
            ResultSet rs = helper.excecuteQuery(sql);
            while (rs.next()) {
                LopPOJO l = new LopPOJO();
                l.setMaLop(rs.getString("MaLop"));
                l.setKhoi(rs.getString("Khoi"));
                l.setMaNamHoc(rs.getString("MaNamHoc"));
                list.add(l);
            }
            helper.close();

        } catch (Exception ex) {
            System.out.print(ex.getMessage());
        }
        return list;
    }
    public static ArrayList<LopPOJO> selectLop_Nam(String NamHoc){
        ArrayList<LopPOJO> list = new ArrayList<LopPOJO>();
        try {
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            helper.open();

            String sql = "SELECT * "
                    + "FROM dslop l join dsnamhoc nh on l.MaNamHoc=nh.MaNamHoc  "
                    + "WHERE NamHoc LIKE '"+NamHoc+"'";
            ResultSet rs = helper.excecuteQuery(sql);
            while (rs.next()) {
                LopPOJO l = new LopPOJO();
                l.setMaLop(rs.getString("MaLop"));
                l.setKhoi(rs.getString("Khoi"));
                l.setMaNamHoc(rs.getString("MaNamHoc"));
                list.add(l);
            }
            helper.close();

        } catch (Exception ex) {
            System.out.print(ex.getMessage());
        }
        return list;
    }
    public static ArrayList<LopPOJO> layLop() {
        ArrayList<LopPOJO> list = new ArrayList<LopPOJO>();
        try {
            String sql = "SELECT* FROM dslop";
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            //ket noi
            helper.open();
            //rut trich du lieu
            ResultSet rs = helper.excecuteQuery(sql);
            while (rs.next()) {
                LopPOJO l = new LopPOJO();
                l.setMaLop(rs.getString("MaLop"));
                l.setKhoi(rs.getString("Khoi"));
                l.setMaNamHoc(rs.getString("MaNamHoc"));
                list.add(l);
            }
            helper.close();

        } catch (Exception ex) {
            System.out.print(ex.getMessage());
        }
        return list;
    }
    public static boolean ThemLop(String MaLop,String Khoi,String MaNamHoc ) {
        boolean kq = false;
        try {
            String sql = String.format("INSERT INTO dslop(MaLop,Khoi,MaNamHoc) VALUES('%s',%s,'%s')",MaLop,Khoi,MaNamHoc);
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            //ket noi
            helper.open();
            int a = helper.excecuteUpdate(sql);
            if(a==1){
                kq = true;
            }
            helper.close();

        } catch (Exception ex) {
            System.out.print(ex.getMessage());
        }
        return kq;
    }



    public static boolean  XoaLop(String maLop, String maNamHoc) {
        boolean kq = false;
        try {

            String sql = String.format("DELETE FROM dslop WHERE MaLop = '%s' AND MaNamHoc = '%s'",maLop,maNamHoc);
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            //ket noi
            helper.open();
            int a = helper.excecuteUpdate(sql);
            if(a==1){
                kq = true;
            }
            helper.close();
        } catch (Exception ex) {
            System.out.print(ex.getMessage());
        }
        return kq;
    }
     public static boolean  suaLop(String MaLop,String Khoi,String MaNamHoc) {
        boolean kq = false;
        try {
            String sql = String.format("UPDATE dslop SET MaLop='%s',Khoi='%s' WHERE MaNamHoc='%s'",MaLop,Khoi,MaNamHoc);
            MySQLDataAccessHelper helper = new MySQLDataAccessHelper();
            //ket noi
            helper.open();
            int a = helper.excecuteUpdate(sql);
            if(a==1){
                kq = true;
            }
            helper.close();

        } catch (Exception ex) {
            System.out.print(ex.getMessage());
        }
        return kq;
    }
}
